﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Maknys.Consultator.DomainModel;
using Maknys.Consultator.DataContracts;
using System.Data.SqlClient;
using System.Collections;

namespace Maknys.Consultator.Repositories
{
    public class EditingRepository : IEditingRepository
    {
        private String connectionString = ("Data Source=ERNESTAS-PC\\SQLEXPRESS;Initial Catalog=Consultator;Integrated Security=True");

        public void ShowAll(ArrayList elementList)
        {
            elementList.Clear();
            int RowsNumber = CountRowsInTable();
            for (int i = 0; i < RowsNumber; i++)
            {
                ReadAll(i + 1, elementList);
            }
        }

        public void ReadAll(int id, ArrayList infoList)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                String query = "SELECT * FROM Question4 WHERE Id = " + id;
                using (var command = new SqlCommand(query, connection))
                {
                    var reader = command.ExecuteReader();
                    int Id = 0;
                    while (reader.Read())
                    {
                        Id = reader.GetInt32(0);
                        int PId = reader.GetInt32(1);
                        String Value = reader.GetString(2);
                        string newSentence = makeValueSentence(Value);

                        Element info = new Element();
                        info.Id = Id.ToString();
                        info.PId = PId.ToString();
                        info.Value = newSentence;
                        infoList.Add(info);
                    }
                    connection.Close();
                    reader.Close();
                }
            }
        }

        public string makeValueSentence(string item)
        {
            int cnt = 0;
            int cut = 0;
            bool doit = true;
            while (doit)
            {
                if (item[cnt] != ' ' && item[cnt + 1] == ' ' && item[cnt + 2] == ' ')
                {
                    cut = cnt;
                    doit = false;
                }
                cnt++;
            }
            string finalSentence = item.Substring(0, cut+1);
            return finalSentence;
        }

        public void Edit(string id, string pId, string value)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("UPDATE Question4 SET PId=@PId, Value=@Value" + " WHERE Id=@Id", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Parameters.AddWithValue("@PId", pId);
                    cmd.Parameters.AddWithValue("@Value", value);
                    int rows = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void Remove(string id)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Question4 " + "WHERE Id=@Id", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    int rows = cmd.ExecuteNonQuery();
                }
            }
        }

        public void Create(string pId, string value)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Question4 VALUES(" + "@PId, @Value)", conn))
                {
                    cmd.Parameters.AddWithValue("@PId", pId);
                    cmd.Parameters.AddWithValue("@Value", value);
                    int rows = cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
        
        public int CountRowsInTable()
        {
            string stmt = "SELECT MAX(Id) FROM Question4";
            int maxId = 0;
            using (SqlConnection thisConnection = new SqlConnection(connectionString))
            using (SqlCommand cmdCount = new SqlCommand(stmt, thisConnection))
            {
                thisConnection.Open();
                maxId = Convert.ToInt32(cmdCount.ExecuteScalar());
                thisConnection.Close();
            }
            return maxId;
        }

        public void MakeSentence(string sentence, ref string id, ref string pId, ref string value)
        {
            int cnt = 0;
            while (sentence[cnt] != ' ')
            {
                cnt++;
            }
            id = sentence.Substring(0, cnt);
            cnt++; cnt++;
            int previous = cnt;
            while (sentence[cnt] != ' ')
            {
                cnt++;
            }
            pId = sentence.Substring(previous, cnt - previous);
            cnt++;
            value = sentence.Substring(++cnt, sentence.Length - cnt);
        }
    }
}
